﻿<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExecuteExcel.aspx.cs" Inherits="ExecuteExcel" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
   <script language="javascript">
		
		var alphabets = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
		var CharIndex = "A";
		var indexOfLetter;

		function incrementAlphabet(alphaIncrement)
		{
			indexOfLetter = alphabets.search(alphaIncrement);
			if (indexOfLetter+1 < alphabets.length) 
			{
				return(alphabets.charAt(indexOfLetter+1));
			}
			else
			{
				return(alphaIncrement);
			}
		}
		
    function CreatePivotTableChart()
    {
	
		var ExlApp = new ActiveXObject("Excel.Application");
		ExlApp.Visible = false;
		ExlApp.DisplayAlerts = false;

		var WorkBook = ExlApp.Workbooks.Add();
		var Sheet = WorkBook.ActiveSheet;
		var Sheet2 = WorkBook.Worksheets(2);
		Sheet2.Cells(1,1).value="BÁO CÁO DOANH SỐ NGÀY";
	    Sheet2.Cells(2,1).value="Ngày giờ lấy báo cáo";
        Sheet2.Cells(2,4).value=Date();
		var rowCount = document.getElementById("hdnRowCount").value;
		var colCount = document.getElementById("hdnColCount").value;
		var arrHead = document.getElementById("hdnHeading").value.split("$");
		var arrOrnt = document.getElementById("hndOrientation").value.split("$");
		var tempVariable;
		var i;
		var j;
		
		for(j=0; j <  parseInt(colCount) ;j++)
		{
			for(i=0; i < parseInt(rowCount); i++)
			{
				letterCount = 	CharIndex + parseInt(i + 1);
				if(i == 0)
				{
					Sheet.Range(letterCount).Value = arrHead[j];
					Sheet.Range(letterCount).Interior.ColorIndex = 10;
					Sheet.Range(letterCount).Font.ColorIndex = 6;	
				}
				else
				{
						Sheet.Range(letterCount).Value = PivotList[i - 1][j];
				}
						
									
			}
			CharIndex = incrementAlphabet(CharIndex);		
		}	
		var a;
		var tempString;
		var rowCountTemp = parseInt(rowCount) + 1;
		var ptr = WorkBook.PivotCaches()
		var pc = ptr.Add(1, "Sheet1!R1C1:R" + rowCountTemp + "C" + colCount)
		pc.CreatePivotTable("Sheet2!R18C1", "PivotTable1");

		with(WorkBook.Worksheets(2).PivotTables("PivotTable1"))
		{   
    				
    				for(i=0; i< parseInt(arrHead);i++)
					{
						with(PivotFields(arrHead[i]))
						{
							Orientation = arrOrnt[i];
							Name=arrHead[i];
							a = Name;
							tempString = Name;
						}
					}
		}
		
		i=0;
		if(i < arrOrnt.length)
		{
			while(parseInt(i) < arrOrnt.length)
			{		
					with(WorkBook.Worksheets(2).PivotTables("PivotTable1"))
					{  
						with(PivotFields(arrHead[i]))
						{	
							if(parseInt(arrOrnt[i]) >=4)
							{
								Orientation = 4;
															
							}
							else
							{
								Orientation = parseInt(arrOrnt[i]);	
							}
														
							tempString = Name;
							a = a + "$" + Name;
					   }
				   }
											
				WorkBook.Worksheets(2).Cells.Find(tempString).Interior.ColorIndex = 10;
				WorkBook.Worksheets(2).Cells.Find(tempString).Font.ColorIndex = 6;	
				i++;				
			}
		}
		
	    WorkBook.Worksheets(2).PivotTables("PivotTable1").PivotFields("Count of OffAmt").Function = -4157;
		WorkBook.Worksheets(2).PivotTables("PivotTable1").DisplayImmediateItems = true;

		WorkBook.Worksheets(2).Name = "Pivot_Table_Report";
		WorkBook.Worksheets(1).Delete();
		WorkBook.Worksheets(2).Delete();
		WorkBook.HTMLProject.RefreshDocument();
		ExlApp.UserControl = false;
		ExlApp.Visible = true;
		window.history.back();
	}
    </script>
   
</head>
<body onload="CreatePivotTableChart()">
    <form id="form1" runat="server">
     <div>
     <asp:ScriptManager ID="ScriptManager1" runat="server">
     </asp:ScriptManager>
         
         <asp:UpdatePanel ID="UpdatePanel1" runat="server">
         <ContentTemplate>
         
        <div>
                
                <input type="hidden" id="hdnRowCount" runat="server" name="hdnRowCount" />
                <br />
                <input type="hidden" id="hdnColCount" runat="server" name="hdnColCount" />
                <br />
                <input type="hidden" id="hndOrientation" runat="server" name="hndOrientation" />
                <br />
                <input type="hidden" id="hdnHeading" runat="server" name="hdnHeading" />
                </div>
               
      </ContentTemplate>
         </asp:UpdatePanel>
               
     </div>
    <table style="width:50%;" align="center">
        <tr>
            <td align="center">
                <img alt="" src="../../../image/load.gif" height="100px" width="100px" />
               
            </td>
        </tr>
        <tr>
            <td align="center">
                Creating file Excel Pivot....</td>
        </tr>
    </table>
               
    </form>
    </body>
</html>
